﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using Vote.DataEntity;

namespace Vote.Database.DataAccess
{
    public class ResultAccess
    {
        /// <summary>
        /// 获取投票统计结果
        /// </summary>
        /// <returns></returns>
        public List<ResultDataEntity> GetVoteResult(int actionId)
        {
            List<ResultDataEntity> voteResultList = new List<ResultDataEntity>();

            string sql = @"SELECT P1.ActionID, P1.Actionname, P1.Begintime, P1.EndTime, P1.GroupId, P1.GroupName, P1.Itemid, P1.ItemName, T.VoteCount
                            FROM ItemView P1
                            LEFT JOIN 
                            (
                            SELECT count(1) AS VoteCount ,Itemid from result GROUP BY itemid 
                            ) T
                            ON T.Itemid = P1.Itemid
                            WHERE P1.ActionID = ?actionId";

            var parms = new MySqlParameter[] { new MySqlParameter("actionId", MySqlDbType.String) };
            parms[0].Value = actionId;
            using (var reader = MySqlHelper.ExecuteReader(MySqlHelper.ConnectionStringManager, CommandType.Text, sql, parms))
            {
                while (reader.Read())
                {
                    var voteResult = new ResultDataEntity()
                    {
                        ActionID = Convert.ToInt32(reader["ActionID"]),
                        Actionname = reader["Actionname"] != DBNull.Value ? reader["Actionname"].ToString() : string.Empty,
                        BeginTime = reader["Begintime"] != DBNull.Value ? DateTime.Parse(reader["Begintime"].ToString()) : DateTime.MinValue,
                        EndTime = reader["EndTime"] != DBNull.Value ? DateTime.Parse(reader["EndTime"].ToString()) : DateTime.MinValue,

                        GroupID = Convert.ToInt32(reader["GroupID"]),
                        GroupName = reader["GroupName"] != DBNull.Value ? reader["GroupName"].ToString() : string.Empty,
                        ItemID = Convert.ToInt32(reader["Itemid"]),
                        ItemName = reader["ItemName"] != DBNull.Value ? reader["ItemName"].ToString() : string.Empty,
                        VoteCount = reader["VoteCount"] != DBNull.Value ? Convert.ToInt32(reader["VoteCount"]) : 0,
                    };

                    voteResultList.Add(voteResult);
                }
            }

            return voteResultList;
        }

        /// <summary>
        /// 获取投票统计结果
        /// </summary>
        /// <returns></returns>
        public List<ResultDataEntity> GetVoteResult(string actionlist)
        {
            List<ResultDataEntity> voteResultList = new List<ResultDataEntity>();
            string sql = @"SELECT P1.ActionID, P1.Actionname, P1.Begintime, P1.EndTime, P1.GroupId, P1.GroupName, P1.Itemid, P1.ItemName, T.VoteCount
                            FROM ItemView P1
                            LEFT JOIN 
                            (
                            SELECT count(1) AS VoteCount ,Itemid from result GROUP BY itemid 
                            ) T
                            ON T.Itemid = P1.Itemid
                            WHERE P1.ActionID in (" + actionlist + ") ORDER BY P1.Actionid DESC";

            //var parms = new MySqlParameter[] { new MySqlParameter("actionlist", MySqlDbType.String) };
            //parms[0].Value = actionlist;
            using (var reader = MySqlHelper.ExecuteReader(MySqlHelper.ConnectionStringManager, CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    var voteResult = new ResultDataEntity()
                    {
                        ActionID = Convert.ToInt32(reader["ActionID"]),
                        Actionname = reader["Actionname"] != DBNull.Value ? reader["Actionname"].ToString() : string.Empty,
                        BeginTime = reader["Begintime"] != DBNull.Value ? DateTime.Parse(reader["Begintime"].ToString()) : DateTime.MinValue,
                        EndTime = reader["EndTime"] != DBNull.Value ? DateTime.Parse(reader["EndTime"].ToString()) : DateTime.MinValue,

                        GroupID = Convert.ToInt32(reader["GroupID"]),
                        GroupName = reader["GroupName"] != DBNull.Value ? reader["GroupName"].ToString() : string.Empty,
                        ItemID = Convert.ToInt32(reader["Itemid"]),
                        ItemName = reader["ItemName"] != DBNull.Value ? reader["ItemName"].ToString() : string.Empty,
                        VoteCount = reader["VoteCount"] != DBNull.Value ? Convert.ToInt32(reader["VoteCount"]) : 0,
                    };

                    voteResultList.Add(voteResult);
                }
            }

            return voteResultList;
        }

        /// <summary>
        /// 根据ItemID获取用户ID列表
        /// </summary>
        /// <param name="itemId"></param>
        /// <returns></returns>
        public List<string> GetUserIDListByItemID(int itemId)
        {
            List<string> userIdList = new List<string>();

            string sql = @"SELECT P2.UserId FROM result P1 INNER JOIN vote P2 ON P2.Id = P1.Voteid WHERE P1.Itemid = ?itemId";

            var parms = new MySqlParameter[] { new MySqlParameter("itemId", MySqlDbType.String) };
            parms[0].Value = itemId;
            using (var reader = MySqlHelper.ExecuteReader(MySqlHelper.ConnectionStringManager, CommandType.Text, sql, parms))
            {
                while (reader.Read())
                {
                    var userId = reader["UserId"] != DBNull.Value ? reader["UserId"].ToString() : string.Empty;

                    userIdList.Add(userId);
                }
            }

            return userIdList;
        }

        /// <summary>
        /// 根据ActionID获取用户列表
        /// </summary>
        /// <param name="voterLists"></param>
        /// <returns></returns>
        public List<VoterDataEntity> GetVotersByActionID(int actionId)
        {
            List<VoterDataEntity> voterList = new List<VoterDataEntity>();

            string sql = @"SELECT P1.UserId, P1.Createtime, P3.Itemname,P3.Itemkey, P4.Groupname FROM vote P1 
                        INNER JOIN result P2 ON P2.Voteid = P1.Id INNER JOIN item P3 ON P3.Id = P2.Itemid INNER JOIN `group` P4 ON P4.Id = P3.Groupid
                        WHERE P1.Actionid = ?actionId ORDER BY P4.Id, P3.Id";

            var parms = new MySqlParameter[] { new MySqlParameter("actionId", MySqlDbType.String) };
            parms[0].Value = actionId;
            using (var reader = MySqlHelper.ExecuteReader(MySqlHelper.ConnectionStringManager, CommandType.Text, sql, parms))
            {
                while (reader.Read())
                {
                    var voter = new VoterDataEntity();
                    voter.UserId = reader["UserId"] != DBNull.Value ? reader["UserId"].ToString() : string.Empty;
                    voter.Createtime = reader["Createtime"] != DBNull.Value ? DateTime.Parse(reader["Createtime"].ToString()) : DateTime.MinValue;
                    voter.Itemkey = reader["Itemkey"] != DBNull.Value ? reader["Itemkey"].ToString() : string.Empty;
                    voter.Itemname = reader["Itemname"] != DBNull.Value ? reader["Itemname"].ToString() : string.Empty;
                    voter.Groupname = reader["Groupname"] != DBNull.Value ? reader["Groupname"].ToString() : string.Empty;

                    voterList.Add(voter);
                }
            }

            return voterList;
        }
    }
}
